

ALTER PROCEDURE [dbo].[sp_record290]
(
	@varStartPeriod DATETIME,	--MONTH
	@varEndPeriod DATETIME
)

AS
	DECLARE @varRecordType Varchar(15)
	DECLARE @varCompanyCode Varchar(15)
	DECLARE @varTalxClientId Varchar(15)

BEGIN

	SELECT @varRecordType = column_value
	FROM tx_reference_values
	WHERE record_type = 'tx_290empltaxcrdt'
	AND column_name = 'rectype'
	
	SELECT @varCompanyCode = column_value
	FROM tx_reference_values
	WHERE record_type = 'tx_290empltaxcrdt'
	AND column_name = 'cocode'
	
	SELECT @varTalxClientId = column_value
	FROM tx_reference_values
	WHERE record_type = 'tx_290empltaxcrdt'
	AND column_name = 'tci_id'
	
	CREATE TABLE #TMP_290RECORD
	(
	rectype   char (15),
	cocode   char (16),
	ssn   char (11),
	filler   char (64),
	tci_id   char (5),
	filler_1   char (100),
	wotclocnumber   char (25),
	ticsworksitelocnumber   char (25),
	firstdayworked   char (8),
	lastcheckdate   char (8),
	ytdhours   char (7),
	ytdtips   char (12),
	ytdeicadvance   char (12),
	ytdwages   char (13),
	ytdwagesfuta   char (13),
	fein   char (15),
	payperiodenddt   char (8),
	hourlywage   char (6),
	EMPNO VARCHAR(15),
	SequenceNumber INT,
	CheckNumber VARCHAR(50),
	EmpKey INT,
	Rate VARCHAR(50)
	)
	
	INSERT INTO #TMP_290RECORD
	(
			rectype, cocode,  tci_id,  wotclocnumber,  firstdayworked,  lastcheckdate , 
			ytdhours,  ytdtips,  ytdeicadvance,  ytdwages,  ytdwagesfuta ,  fein,  payperiodenddt,  hourlywage,
			EMPNO,  SequenceNumber,  CheckNumber ,  EmpKey,  Rate
	)
	SELECT	@varRecordType,		@varCompanyCode,	@varTalxClientId,	L.LOCATION,	H.PeriodStartDate, 	H.CheckDate,	E.YTDHours,	'0', '0', H.YTDGrossPay, H.YTDTaxableWages, 
H.EmployeeNumber, H.PeriodEndDate, E.Rate, H.EmpKey, E.SequenceNumber, 	H.CheckNumber, H.EmpKey,	E.Rate
	FROM Peoplesoft.dbo.PS_LOCATION_TBL L , PEOPLESOFT.DBO.Emp_Check2Earnings E, PEOPLESOFT.DBO.Emp_Check2Header H
	WHERE L.LOCATION = E.ChargeToDiv
	AND H.EmpKey = E.EmpKey
	AND H.CheckDate = E.CheckDate
	AND H.SequenceNumber = E.SequenceNumber
	AND CONVERT(DATE, H.CheckDate) BETWEEN @varStartPeriod AND @varEndPeriod
	
	UPDATE T
	SET t.ssn = m.SSN
	FROM #TMP_290RECORD T, PEOPLESOFT.DBO.EMP_MAIN M
	WHERE T.EMPNO = M.EmpNo
	
	-----------------------------------
	--HANDLE WORKSITE (WHICH MAY BE CHARGETODEPARTMENT FIELD)
	-----------------------------------
	


	-----------------------------------
	--INSERT DATA IN RECORD 290 TABLE
	-----------------------------------
	
	SELECT *
	FROM #TMP_290RECORD
	
	--DROP TABLE #TMP_290RECORD
	
END
